package com.erp.app.catalog.catgroup.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.erp.app.catalog.catgroup.criteria.CatalogGroupCriteria;
import com.erp.app.catalog.catgroup.pk.CatalogGroupPK;
import com.erp.app.catalog.catgroup.vo.CatalogGroupVO;
import com.erp.app.common.ProjectConstants;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.dao.SimpleSearchMaintStyleDao;
import com.erp.framework.model.pk.SimpleBasePk;

import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.model.dao.ServiceLocator;
import com.erp.framework.util.CommonUtil;
import com.erp.framework.util.logger.Logger;

public class CatalogGroupDAO extends SimpleSearchMaintStyleDao {

	/**
	* Returns the SQL select statement.
	* @return        a string containing sql query
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlSelectByPrimaryKey(){

		return sqlFind();
	}

	/**
	* Sets the values of a result set to a pk
	* @param         pStmt   the incoming prepared statement
	* @param         pk      the searching primary key
	* @return        a vo containing the result set values
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForSelectByPrimaryKey(PreparedStatement pStmt, SimpleBasePk pk) throws SQLException{

		setValuesForFind(pStmt, pk);
	}

	/**
	* Returns the constructed model from a result set.
	* @param         rs     the incoming result set
	* @return        a constructed VO from the result set
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected SimpleBaseVo getModelForSelectByPrimarKey(ResultSet rs) throws SQLException {

		return getResultSetValues(rs);
	}


	/**
	* Returns the SQL select statement.
	* @param         criteria     the searching criteria
	* @return        a string containing sql query
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlSelectByCriteria(SimpleBaseSrchCriteria criteria) {

		return sqlQuery(criteria);
	}

	/**
	* Returns the constructed model from a result set.
	* @param         rs     the incoming result set
	* @return        a constructed VO from the result set
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected SimpleBaseVo getModelForSelectByCriteria(ResultSet rs) throws SQLException {

		return getResultSetValues(rs);
	}

	/**
	* Returns the constructed primary key from a result set.
	* @param         rs     the incoming result set
	* @return        a constructed PK from the result set
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected SimpleBasePk getPkForSelectByCriteria(ResultSet rs) throws SQLException {

		return null;
	}


	/**
	* Returns the SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing sql query
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlQuery(SimpleBaseSrchCriteria obj) {
		String sql = "";

		return sql;
	}

	/**
	* Returns the where clause of a SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing the where clause
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String toWhereClause(CatalogGroupCriteria crit) {

		CatalogGroupCriteria criteria = (CatalogGroupCriteria) crit;
		StringBuffer whereClause = new StringBuffer();



		return whereClause.toString();
	}
	
	
  public ChildList selectByParent(SimpleBaseVo parent) throws FindRecordException {
  	ChildList childs = null;
  	Connection conn = null;
  	try {
  		if (parent.getPrimaryKey() != null) {
  			conn = ServiceLocator.getInstance().getDSDBConnection();
  			childs = selectByParent(parent, conn);
  			}
  		} catch (Exception e) {
  			throw new FindRecordException("[" + parent.getClass().getName() + "]: " + e.getMessage());
  		} finally {
  				try {
  					if (conn != null && !conn.isClosed()) conn.close();
  					} catch (SQLException e) {
  						
  				}
  		}
  		return childs;
  	}
    	
	public ChildList selectByParent(SimpleBaseVo parent, Connection conn)
		throws FindRecordException {
    	PreparedStatement pStmt = null;
        ResultSet rs = null;
        
        CatalogGroupVO parentvo = (CatalogGroupVO) parent;
        
        ChildList rslt = new ChildList(CatalogGroupVO.class);
        try {
            if(CommonUtil.isEmpty(parentvo.getL01nodid())){
                pStmt = conn.prepareStatement(sqlListSuper(parentvo.getUserId()));
            }else if(parentvo.getL01lvl().intValue()==1){
                pStmt = conn.prepareStatement(sqlList(parentvo.getUserId()));
            }else if(parentvo.getL01lvl().intValue()==2){
                pStmt = conn.prepareStatement(sqlListsub(parentvo.getUserId()));
            }else if(parentvo.getL01lvl().intValue()==3){
                return null;
            }            
            
            setValuesForList(pStmt, parentvo.getPrimaryKey());
            rs = pStmt.executeQuery();

            while (rs.next()) {
                CatalogGroupVO catalogGroupVO = (CatalogGroupVO) getResultSetValues(rs);
                catalogGroupVO.setParent(parentvo);
                catalogGroupVO.setIsExisting(true);
                catalogGroupVO.setIsDirty(false);

                rslt.add(catalogGroupVO);
            }
        } catch (SQLException sqe) {
            throw new FindRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null) rs.close();
                if (pStmt != null) pStmt.close();
                
                
            } catch (SQLException e) {
            }
        }
        return rslt;
	}

	/**
	* Constructs a string of SQL insert statement
	* @return        a string of SQL insert statement
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlInsert() {

		String sql = "INSERT INTO T_CATEGORY(NODE_ID ,DESCRIPTION	,IMAGE_ID ,CATALOG_LEVEL ,TYPE ,CATEGORY_ID	,"+
               "CREATE_BY ,CREATE_DATE ,CREATE_COMPANY_CODE	,ACTIVE)	"+
               " VALUES(?,?,?,?,?,?,?,?,?,?,?)";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL insert or update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType)
		throws SQLException {

    CatalogGroupVO vo=(CatalogGroupVO) obj;
    int i=1;
    setNullableString(pStmt,i++,vo.getL01dsc());
    setNullableBigDecimal(pStmt,i++,vo.getL01imgid());
    setNullableBigDecimal(pStmt,i++,vo.getL01lvl());
    setNullableString(pStmt,i++,vo.getL01typ());
    setNullableBigDecimal(pStmt,i++,vo.getL01catid());
    setNullableString(pStmt,i++,vo.getCreateUser());
    setNullableTimestamp(pStmt,i++,vo.getCreateDate());
    setNullableBigDecimal(pStmt,i++,vo.getL01audid());
    setNullableBigDecimal(pStmt,i++,vo.getL01act());
    setNullableBigDecimal(pStmt,i++,vo.getL01nodid());

	}

	/**
	* Sets the values for a SQL insert statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForInsert(PreparedStatement pStmt, Object obj)
		throws SQLException {

		setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
	}

	/**
	* Constructs a string of SQL update statement
	* @return        a string of SQL update statement
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlUpdate() {

		String sql = "UPDATE T_CATEGORY set DESCRIPTION=?	,IMAGE_ID=?	,CATALOG_LEVEL=? ,TYPE=? ,CATEGORY_ID=?	,"+
               "CREATE_BY=?	,CREATE_DATE=?	,CREATE_COMPANY_CODE=?	,AUDIT_ID=?	,ACTIVE=?	"+
               " WHERE NODE_ID=?";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForModify(PreparedStatement pStmt, Object obj)
		throws SQLException {

		setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
	}

	/**
	* Constructs a string of SQL delete statement
	* @return        a string of SQL update statement
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlDelete() {
		String sql = "DELETE FROM T_CATEGORY WHERE NODE_ID=? ";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL delete statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForDelete(PreparedStatement pStmt, Object obj)
		throws SQLException {
      CatalogGroupVO vo=(CatalogGroupVO) obj;
      int i=1;
      pStmt.setBigDecimal(i++,vo.getL01nodid());
	}

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlFind() {

		String sql = "SELECT NODE_ID ,DESCRIPTION ,IMAGE_ID ,CATALOG_LEVEL ,TYPE ,CATEGORY_ID	,"+
               "CREATE_USER ,CREATE_DATE , COMPANY_CODE ,L01ACT , BROWSER"+
               " FROM T_CATALOG WHERE NODE_ID=?";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL select statement for finding by primary key.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming PK
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForFind(PreparedStatement pStmt, Object obj)
		throws SQLException {
	    CatalogGroupVO vo=(CatalogGroupVO) obj;
	    int i=1;
	    pStmt.setBigDecimal(i++,vo.getL01nodid());
	}

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlList() {

		return null;
	}

	/**
	* Sets the values for a SQL select statement for finding child tables.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming parent PK
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForList(PreparedStatement pStmt, Object obj)
		throws SQLException {
      if(obj!=null){
          CatalogGroupPK pk = (CatalogGroupPK) obj;
          int i = 1;
          pStmt.setBigDecimal(i++,pk.getNodeId());
      }
	}

	/**
	* Sets the values of a result set to a vo for a search screen
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected SimpleBaseVo getSearchResultSetValues(ResultSet rs) throws SQLException {


		return getResultSetValues(rs);
	}

	/**
	* Sets the values of a result set to a vo
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {

		CatalogGroupVO vo = new CatalogGroupVO();

    vo.setL01nodid(rs.getBigDecimal("NODE_ID"));
    
    
    
    vo.setCreateUser(rs.getString("CREATE_USER"));
    vo.setL01crecomcod(rs.getString("COMPANY_CODE"));
    vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
    vo.setL01dsc(rs.getString("DESCRIPTION"));
    
    vo.setL01lvl(rs.getBigDecimal("CATALOG_LEVEL"));
    
    vo.setUserId(rs.getString("USERID"));
    vo.setL01bwr(CommonUtil.bigDecimalToStr(rs.getBigDecimal("BROWSER")));

		return vo;
	}
	
    protected String sqlListSuper(String userId) {
        // TODO Auto-generated method stub    	
    	String sql = "SELECT hdr1.NODE_ID,hdr1.DESCRIPTION, hdr1.CATALOG_LEVEL,"+
               " hdr1.CREATE_USER ,hdr1.CREATE_DATE ,hdr1.COMPANY_CODE ," +
               " hdr1.STATUS , '"+ userId + "' USERID, hdr1.BROWSER " + 
               " FROM T_CATALOG hdr1" +
               " WHERE  hdr1.CATALOG_LEVEL = 1 AND  hdr1.BROWSER = " + ProjectConstants.CATALOG_BWR_SHOW + 
               " and not exists (select 1 from T_CATALOG_SECURITY_DTL b " +
               "where hdr1.NODE_ID = b.NODE_ID and b.USER_CODE = '" + userId + "' )" +
               " order by hdr1.DESCRIPTION, hdr1.NODE_ID";
    	Logger.sql("sqlListSuper : "+sql);
    	return sql;
    }

    protected String sqlList(String userId) {
        // TODO Auto-generated method stub
    	String sql = " SELECT HDR2.NODE_ID,HDR2.DESCRIPTION,HDR2.CATALOG_LEVEL,"+
               " HDR2.CREATE_USER	,HDR2.CREATE_DATE ,HDR2.COMPANY_CODE ," +
               " HDR2.STATUS , '"+ userId + "' USERID, hdr2.BROWSER" + 
               " FROM T_CATALOG HDR1, T_CATALOG_CHILDREN DTL1,T_CATALOG HDR2"+
               " WHERE HDR1.NODE_ID = DTL1.NODE_ID"+
               " AND HDR1.CATALOG_LEVEL = 1"+
               " AND HDR1.NODE_ID = ?"+
               " AND HDR2.NODE_ID = DTL1.CHILD_NODE_ID"+
               " AND hdr2.CATALOG_LEVEL = 2 AND  hdr2.BROWSER = " +  ProjectConstants.CATALOG_BWR_SHOW + 
               " AND  hdr1.BROWSER = " + ProjectConstants.CATALOG_BWR_SHOW + 
               "  and not exists (select 1 from T_CATALOG_SECURITY_DTL b " +
               "where hdr2.NODE_ID = b.NODE_ID and b.USER_CODE = '" + userId + "' )" +
               " order by hdr2.DESCRIPTION, hdr2.NODE_ID ";
    	Logger.sql("sqlList : "+sql);
    	return sql;
    }

    protected String sqlListsub(String userId) {
        // TODO Auto-generated method stub
    	String sql = "SELECT HDR3.NODE_ID,HDR3.DESCRIPTION,HDR3.CATALOG_LEVEL,"+
               " HDR3.CREATE_USER	,HDR3.CREATE_DATE ,HDR3.COMPANY_CODE,"+
               " HDR3.STATUS, '"+ userId + "' USERID, hdr3.BROWSER " + 
               " FROM   T_CATALOG hdr1,T_CATALOG_CHILDREN dtl1,T_CATALOG_CHILDREN dtl2,T_CATALOG hdr2,T_CATALOG hdr3 "
               + " WHERE  hdr1.NODE_ID = dtl1.NODE_ID "+
               " AND hdr1.NODE_ID = dtl1.NODE_ID "+
               " AND hdr2.NODE_ID = dtl1.CHILD_NODE_ID "+
               " AND hdr2.NODE_ID = dtl2.NODE_ID "+
               " AND hdr3.NODE_ID = dtl2.CHILD_NODE_ID "+
               " AND hdr1.CATALOG_LEVEL = 1 "+
               " AND hdr2.CATALOG_LEVEL = 2 "+
               " AND hdr3.CATALOG_LEVEL = 3 "+
               " AND hdr2.NODE_ID=? AND  hdr1.BROWSER = " 
               + ProjectConstants.CATALOG_BWR_SHOW 
               + " AND  hdr2.BROWSER = " + ProjectConstants.CATALOG_BWR_SHOW 
               + " AND  hdr3.BROWSER = " + ProjectConstants.CATALOG_BWR_SHOW
               + " and not exists (select 1 from T_CATALOG_SECURITY_DTL b " +
               "where hdr3.NODE_ID = b.NODE_ID and b.USER_CODE = '" + userId + "' )" +
               " order by HDR3.DESCRIPTION, hdr3.NODE_ID ";
        Logger.sql("sqlListsub : "+sql);
    	return sql;
    }


}
